Database Design You are going to design a database system for a small university.   The database will be used for...

Database Design

You are going to design a database system for a small university.   The database will be used for scheduling classes.   The university consists of multiple schools, which each having multiple departments.  

For this assignment I want you to use a crows-foot notation.   You will need to:

o Define the tables that you need in the database

o Define the attributes that need to be included in the tables

o More attributes (e.g., unique ID’s) may need to be added to the tables

o Some attributes may need to be split apart (e.g., Name – split into -> LNAME, FNAME, MINITIAL)

o Define the relationships (e.g., 1-to-1, 1-to-many, many-to-many) between the tables

o You will need to identify the primary and foreign keys.

o DO NOT worry about differentiating between strong and weak relationships

The following are the business rules for the university:

1. Each school consists of multiple departments, but a department can only belong to a single school.

2. Each department can offer many different classes and each class offered may have multiple sections.  

3. A class is only offered through one department and each section maps to a single class.

4. A section of a class is assigned to a single classroom at a specific time, but a classroom may have several sections assigned to it.

5. Departments may employ many faculty members and each faculty member is employed by one department.

6. A professor (faculty member) can teach many sections, but a section is taught by a single faculty member.

7. Multiple students can sign up for many sections and sections may have many students.

8. A student has one faculty advisor, but an advisor can advise many students.

Information maintained about the different entities includes:

• 

• School

o School name (e.g., Business, Engineering, etc.)

o Building name (e.g., Gorman, Agnese-Sosa, etc.)

o Office number

o Dean name

o Phone Number

o Mail Stop

• Department

o Department name (e.g., Management Information Systems, Management, etc.)

o Coordinator name

o Building name

o Office number

o Phone number

o Mail stop

• Class

o Course number (e.g., MIS2321)

o Course name (e.g., Introduction to MIS)

• Section

o Unique ID number (e.g., CRN00101)

o Section number (e.g., 1,2,3,4)

• Room

o Building name (e.g., Gorman, ATT, etc.)

o Room name/number (e.g., 107, 104, etc.)

o Capacity

• Faculty

o Faculty ID

o Faculty name

o Building name

o Office number

o Office phone

o Title

• Student

o Student ID Number

o Student Name

o Phone Number

o Address

o Major (e.g., Management Information Systems, Management, etc.)

o Major2

o Minor